Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Design Considerations

Data access patterns give you the most information about how to design the optimal system. Here are some of the I/O characteristics with which you are familiar from previous chapters:

  I/Os are typically the limiting factor in the system. The number of I/Os that can happen is determined by the speed and number of disk drives (see Chapter 14, “Advanced Disk I/O Concepts”).
  I/O can be reduced by caching data blocks in the SGA. If the data you want to access is already in the SGA, a physical disk I/O is not required.
  Isolate sequential I/Os. Most of the time spent reading from or writing to the disk is spent seeking to where the data is. If you can reduce seek times, you can achieve more I/Os per second. In a batch processing system, the load/unload files are all accessed sequentially.
  Spread out random I/Os. Random I/Os have a maximum rate per drive. By spreading the I/Os over many drives, you can increase the overall rate.
  Avoid paging and swapping. Any time the system pages or swaps, performance is severely degraded. Avoid this at all costs.

Many or all of these factors may apply in your batch processing system, depending on the type of batch processing you do. The physical layout as well as SGA and shared-pool tuning can create an optimally configured server for these jobs. As you are aware, the design of the transactions is an integral part of the design of the entire system. The application, the RDBMS, the OS, and the hardware all work together to do the work. Neglecting any of these components can result in performance degradation.

Physical Data Layout

This section examines how the data should be configured on the physical disks for optimal performance in a batch processing environment. First, it looks at how to lay out the data on traditional disks; then it looks at disk arrays. I recommend using disk arrays if at all possible; the ease of use and performance benefits make disk arrays an investment worth making.

The goal in designing the physical data layout is to isolate the sequential I/Os and to balance I/Os across all the disks that are randomly accessed. Because batch processing systems may or may not do significant numbers of updates, you should weigh the options presented in the following sections against your particular configuration.

The recommendations that follow are based on light, moderate, and heavy use of the redo log files (as determined by the amount and intensity of updates to the system). As always, budgetary constraints may not allow everyone to buy the optimal number of disks for the recommended configuration. Make the best of these guidelines. Remember that it is the number of disks that provide performance with random I/O; if you have the opportunity to buy one 4 gigabyte disk drive or two 2 gigabyte disk drives, the best performance comes from the two 2 gigabyte disk drives.

Traditional Disks

The optimal layout for a batch processing system is hemmed in by significant number of “it depends.” Read these guidelines, use what will work for you, and discard the rest. A minimal configuration should look something like this:


Element (# of Disks) Comments

System (1+) The system disk is used for the operating system, swap file (if applicable), user files, and Oracle binaries.
Redo log (0) Light update activity. If there is little update activity, the redo log files do not see much I/O and are not a bottleneck. The redo log files can reside on the same disk as the system to save space; reallocate the disks for data files.
Redo log (2+) Moderate to heavy update activity. If there is moderate to heavy update activity, it is much more important to protect the redo logs and provide for some sort of fault tolerance. The number of disks you need depends on the extent of the updates.
Archive logs (0) Light update activity. As with the redo log files, archiving is minimal and not necessary in systems with little update activity. The archive log files can reside on the system disk.
Archive logs (1+) Moderate to heavy update activity. As with redo log files, if updates are done on this system, you must provide for the sequential nature of the archive log files.
Data files (1+) The number of disks you need for data is determined by the amount of random I/O your user community generates.
Index files (1+) The number of disks needed for indexes is determined by the size of the indexes and the number of I/Os to the indexes.

Both the data files and the indexes should be striped over as many disk drives as necessary to achieve optimal I/O rates on those disks. From Chapter 14, “Advanced Disk I/O Concepts,” remember that you can push a disk drive only to a maximum random I/O rate.

In earlier chapters, you learned that the data and indexes can be striped across the disks using Oracle or RAID striping or a combination of the two. I recommend a hardware disk array over manual Oracle striping primarily because the disk array is easier to use and has better performance. When you use a disk array, the task of distributing I/Os is greatly simplified.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.